<?php
class QBForecast
{	
	public function __construct($p_strCountry)
	{
		$this->_country	= $p_strCountry;
		$this->_table	= strtolower($p_strCountry).'_forecasts';
	}
	
	public function table()
	{
		return $this->_table;
	}
	
	public function get_all()
	{
		$strQuery	= '
			SELECT sku
			FROM '.$this->_table.'
			';
		$db 	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function get_all_for_image()
	{
		$strQuery	= 'SELECT sku FROM '.$this->_table .' Where sku NOT IN (Select sku From '. strtolower($this->_country).'_product_images' .');';
		
		$db 	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	private function _whereSearch($p_arrCategory, $p_arrParams)
	{
		$strQuery		= null;
		if($p_arrCategory){
			$strQuery	= $strQuery.' AND LOWER(t1.category) IN ("'.strtolower(implode('","', $p_arrCategory)).'")';
		}
		
		if(isset($p_arrParams['sku']) && $p_arrParams['sku'] ){
			$strQuery	= $strQuery.' AND ( LOWER(t1.sku) like "%'.strtolower($p_arrParams['sku']).'%"'
				.' OR LOWER(t1.product_name) like "%'.strtolower($p_arrParams['sku']).'%")';
		};
		
		if(isset($p_arrParams['supplier']) && $p_arrParams['supplier'] ){
			$strQuery	= $strQuery.' AND t1.supplier_id ="'.strtolower($p_arrParams['supplier']).'"';
		};
		
		if(isset($p_arrParams['total']) && $p_arrParams['total'] )
		{
			if($p_arrParams['total'] =='11')
			{
				$strQuery	= $strQuery.' AND t1.total_sales > 0 ' ;
			}
			else
			{
				$strQuery	= $strQuery.' AND t1.total_sales > ' . $p_arrParams['total'] . ' ' ;
			}
		};
		
		if(isset($p_arrParams['asl']) && $p_arrParams['asl'] )
		{
			if($p_arrParams['asl'] =='11')
			{
				$strQuery	= $strQuery.' AND t1.asl > 0 ' ;
			}
			else
			{
				$strQuery	= $strQuery.' AND t1.asl > ' . $p_arrParams['asl'] . ' ' ;
			}
		};
		
		if(isset($p_arrParams['campaign']) && $p_arrParams['campaign'] )
		{
			if( $p_arrParams['campaign'] =="1")
			{
				$strQuery	= $strQuery.' AND (campH.start_date <= CURRENT_DATE() AND CURRENT_DATE() <= campH.end_date) ';
			}
			else if( $p_arrParams['campaign'] =="2")
			{
				$strQuery	= $strQuery.' AND (campH.start_date > CURRENT_DATE() AND CURRENT_DATE() < campH.end_date) ';
			}
			else if( $p_arrParams['campaign'] =="3")
			{
				$strQuery	= $strQuery.' AND (campH.start_date < CURRENT_DATE() AND CURRENT_DATE() > campH.end_date) ';
			}
		};
		
		if(isset($p_arrParams['reorder']) && $p_arrParams['reorder'] ){
			$strQuery	= $strQuery.' AND LOWER(t1.reorder)="'.strtolower($p_arrParams['reorder']).'"';
		};
		return $strQuery;
	}
	
	public function get($p_intOffset, $p_intLimit, $p_arrCategory=null, $p_arrParams=null)
	{
		$strQuery	= '
			SELECT t1.*, m.link_image as linkimage, m.name as name, m.description as description, m.brand as brand 
			FROM '.$this->_table.' t1 Left join ' . strtolower($this->_country). '_product_images m ON m.sku  = SUBSTRING_INDEX(t1.sku, "-", 1) ';
			
			if(isset($p_arrParams['campaign']) && $p_arrParams['campaign'] )
			{
				$strQuery	= $strQuery. ' Inner Join  ' . strtolower($this->_country) . "_campaign_skus campD on campD.sku = t1.sku ";	
				$strQuery	= $strQuery. ' Inner Join  ' . strtolower($this->_country) . "_campaigns campH on campH.id = campD.fk_campaign ";
			}
		
		$strQuery	= $strQuery. ' WHERE 1=1 ';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		
		if(isset($p_arrParams['campaign']) && $p_arrParams['campaign'] )
		{
				$strQuery	= $strQuery. " GROUP by t1. sku ";
		}
				
		if( isset($p_arrParams['orf']) &&  $p_arrParams['orf'])
		{
			if($p_arrParams['orv'] =='default')
			{
				$strQuery	= $strQuery .' ORDER BY ' . ' bucket_id ASC, reorder DESC, total_sales DESC ';
			}
			else
			{
				$strQuery	= $strQuery .' ORDER BY '.$p_arrParams['orf'].' '.$p_arrParams['orv'];
			}
		}

		if($p_intLimit!='all')
		{
			$strQuery	= $strQuery.' LIMIT '.$p_intOffset.','.$p_intLimit;
		}
	   
		$db 	= Zend_Registry::get('db');		
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function total($p_arrCategory=null, $p_arrParams=null)
	{
		$strQuery	= '
			SELECT COUNT(t1.id) `total`
			FROM '.$this->_table.' t1
			';
			
		if(isset($p_arrParams['campaign']) && $p_arrParams['campaign'] )
		{
			$strQuery	= $strQuery. ' Inner Join  ' . strtolower($this->_country) . "_campaign_skus campD on campD.sku = t1.sku ";	
			$strQuery	= $strQuery. ' Inner Join  ' . strtolower($this->_country) . "_campaigns campH on campH.id = campD.fk_campaign ";
		}
	
		$strQuery	= $strQuery. ' WHERE 1=1 ';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		$db 		= Zend_Registry::get('db');			
		$arrResult 	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}
	
	public function totalCurrent($p_arrCategory=null, $p_arrParams=null)
	{
		$strQuery	= '
			SELECT COUNT(t1.id) `total`
			FROM '.$this->_table.' t1
			';
		$strQuery	= $strQuery. ' WHERE 1=1 And inventory_check=1 ';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		
		$db 		= Zend_Registry::get('db');
		$arrResult 	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}
	
	public function getCurrent($p_intOffset, $p_intLimit, $p_arrCategory=null, $p_arrParams=null, $export)
	{
		if($export)
		{	
			$strQuery	= '
			SELECT t1.*, m.brand as brand  
			FROM '.$this->_table.' t1 Left join ' . strtolower($this->_country). '_product_images m ON m.sku  = SUBSTRING_INDEX(t1.sku, "-", 1) ';
		}
		else
		{
			$strQuery	= '
			SELECT t1.*, m.link_image as linkimage, m.name as name, m.brand as brand  
			FROM '.$this->_table.' t1 Left join ' . strtolower($this->_country). '_product_images m ON m.sku  = SUBSTRING_INDEX(t1.sku, "-", 1) ';
		}	
		$strQuery	= $strQuery. ' WHERE 1=1 And inventory_check=1 ';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		
		if( isset($p_arrParams['orf']) &&  $p_arrParams['orf'])
		{
			if($p_arrParams['orv'] =='default')
			{
				$strQuery	= $strQuery .' ORDER BY ' . ' bucket_id ASC, reorder DESC, total_sales DESC ';
			}
			else
			{
				$strQuery	= $strQuery .' ORDER BY '.$p_arrParams['orf'].' '.$p_arrParams['orv'];
			}
		}

		if($p_intLimit!='all')
		{
			$strQuery	= $strQuery.' LIMIT '.$p_intOffset.','.$p_intLimit;
		}
		
		$db 	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function get_by_category($p_strCategoryName, $p_strDummy2='C')
	{
		$strQuery	= '
			SELECT 
				f.id, f.sku, f._cancellation_rate, s.supplier_lead_time `dummy1`,
				 s.shipment_type `dummy2`, s.supplier_order_frequency as order_frequency 
			FROM '.$this->_table.' f
				LEFT JOIN '.$this->_country.'_sales s ON f.sku=s.sku			
			WHERE 1=1
		';
		if($p_strCategoryName){
			$strQuery	.= ' AND LOWER(f.category)="'.strtolower($p_strCategoryName).'"';
		}

		if($p_strDummy2){
			$strQuery	.= ' AND LOWER(s.shipment_type)="'.strtolower($p_strDummy2).'"';
		}
		
		$strQuery	.= ' GROUP BY f.sku';
		$db 	= Zend_Registry::get('db');
	    $resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();

		return $resultValue;
	}

	public function get_by_id($p_intId)
	{
		$strQuery	= 'SELECT * FROM '.$this->_table.' WHERE id='.intval($p_intId);
		$db 		= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function insert($p_arrParams)
	{
		$db	= Zend_Registry::get('db');
		$resultValue =$db->insert($this->_table, $p_arrParams);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function update($p_arrParams, $p_intId)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->update($this->_table, $p_arrParams, 'id='.intval($p_intId));
		$db->closeConnection();
		
		//add record to PODetail
		$objPODetail = new QPoDetail($this->_country);
		if(isset($p_arrParams['inventory_check']) && $p_arrParams['inventory_check'])
		{
			$arrData = $objPODetail->getDataForPODetailbyForeCastsId($p_intId);
			if($arrData)
			{
				foreach($arrData as $data)
				{
					if($data['supplier_id'])
					{
						$arrDataOnDetail = $objPODetail->getbySupplierAndSKU($data['supplier_id'], $data['sku']);
						if(!$arrDataOnDetail)
						{	
							$arrParams = array(
								'supplier_id' => $data['supplier_id'],
								'sku'		  => $data['sku'],
								'quantity'	=>$data['final_quantity']
								);	
							$objPODetail->insert($arrParams);
						}
					}
				}	
			}
		}
		return $resultValue;
	}
	
	public function updateCheckboxAll($p_arrParams, $groupId)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = null;
		if($groupId == GROUP_Administrator || $groupId == GROUP_Inventory)
		{
			$resultValue = $db->update($this->_table, $p_arrParams, 'id > '.intval(0));
			
			//add record to PODetail
			$objPODetail = new QPoDetail($this->_country);
			if(isset($p_arrParams['inventory_check']) && $p_arrParams['inventory_check'])
			{
				$objPODetail->truncate();
				$arrData = $objPODetail->getDataForPODetailAll();
				if($arrData)
				{
					foreach($arrData as $data)
					{
						if($data['supplier_id'])
						{
							$arrParams = array(
								'supplier_id' => $data['supplier_id'],
								'sku'		  => $data['sku'],
								'quantity'	  =>$data['final_quantity']
							);	
							$objPODetail->insert($arrParams);
						}
					}	
				}
			}
		}
		else
		{
			$resultValue = $db->update($this->_table, $p_arrParams, 'id > '. intval(0) . ' And buying_check <> 1 Or marketing_check <> 1 ');			
			exit;
		}
		$db->closeConnection();
		return $resultValue;
	}
	
	public function reset()
	{
		$strQuery	= 'TRUNCATE '.$this->_table;
		$db 		= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function confirm()
	{
		$strQuery	= '
			DROP TABLE '.$this->_country.'_archives;
			CREATE TABLE '.$this->_country.'_archives LIKE '.$this->_table.';
			INSERT INTO '.$this->_country.'_archives SELECT * FROM '.$this->_table;
			
		$db 		= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function get_key()
	{
		$strQuery	= 'SELECT * FROM '.$this->_table.' LIMIT 1';
		$db			= Zend_Registry::get('db');
		$arrData	= $db->fetchAll($strQuery);
		$db->closeConnection();
		$arrResult	= array();
		if($arrData && isset($arrData[0]) )
		{
			foreach($arrData[0]	as $key=>$value){
				$arrResult[]	= trim($key);
			}
		}
		return $arrResult;
	}
	
	public function add_column($p_strColumn, $p_strDataType, $p_intAdminId)
	{
		$p_strColumn	= CCommon::format_column_name($p_strColumn);
		$strQuery		= '
			ALTER TABLE '.$this->_table.' 
				ADD COLUMN '.strtolower($p_strColumn).' '.strtolower($p_strDataType).';
				INSERT INTO '.$this->_country.'_columns(icolumn, admin_id, createdate) 
				VALUES("'.strtolower($p_strColumn).'", "'.intval($p_intAdminId).'", sysdate());
			';
		$db			= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function drop_column($p_strColumn, $p_intAdminId)
	{
		$p_strColumn	= CCommon::format_column_name($p_strColumn);
		$strQuery		= '
			ALTER TABLE '.$this->_table.' 
				DROP COLUMN '.strtolower($p_strColumn).';
			DELETE FROM '.$this->_country.'_columns
				WHERE icolumn="'.strtolower($p_strColumn).'";
			';
		$db			= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function dashBoardForecast()
	{
		$db 		= Zend_Registry::get('db');
		//get category	
		$objCategory	= new CCategory($this->_country);
		$arrData		= $objCategory->get();
		$arrCategoryData= isset($arrData['category']) ? $arrData['category'] : null;
		$p_arrCategory	= array();
		
		foreach($arrCategoryData as $data){
				$p_arrCategory[]	= trim($data['name']);
		}
		
		//total sku	
		$strQuery	= 'SELECT count(*) as count FROM '.$this->_table . '
								WHERE LOWER(category) IN ("'.strtolower(implode('","', $p_arrCategory)).'");';
		$arrTotalSku   =  $db->fetchAll($strQuery);
		
		// Buying confirm
		$strQuery	= 'SELECT count(*) as count FROM '.$this->_table . ' WHERE buying_check = 1
								AND LOWER(category) IN ("'.strtolower(implode('","', $p_arrCategory)).'");';
		$arrConfirmBuying   =  $db->fetchAll($strQuery);
		
		//Marketing confirm 
		$strQuery	= 'SELECT count(*) as count FROM '.$this->_table . ' WHERE marketing_check = 1 
								AND LOWER(category) IN ("'.strtolower(implode('","', $p_arrCategory)).'");';		
		$arrConfirmMarketing   =  $db->fetchAll($strQuery);

		//Inventory confirm (Sourcing)
		$strQuery	= 'SELECT count(*) as count FROM '.$this->_table . ' WHERE inventory_check = 1 
								AND LOWER(category) IN ("'.strtolower(implode('","', $p_arrCategory)).'");';		
		$arrConfirmInventory   =  $db->fetchAll($strQuery);
		
		$db->closeConnection();
		
		$arrData = array (
		 'total' => $arrTotalSku[0]['count'],
		 'confirmBuying' => $arrConfirmBuying[0]['count'],
		 'confirmMarketing' => $arrConfirmMarketing[0]['count'],
		 'confirmInventory' => $arrConfirmInventory[0]['count'] 
	   );
	   return $arrData;
	}
	
	public function getForecastsData()
	{
		$db 		= Zend_Registry::get('db');	
		$strQuery	= 'SELECT sku, msl FROM '.$this->_table . ';';
		$arrData   =  $db->fetchAll($strQuery);
		$db->closeConnection();
		return $arrData;
	}
}
?>